

/**********************************************************************
***********************************************************************
***********************************************************************
Labcheck 
***********************************************************************
***********************************************************************
**********************************************************************/




/**********************************************************************
Add Labcheck
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddLabcheck') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddLabcheck
GO

create procedure AddLabcheck
   @name nvarchar(50),
   @isquantified int,
   @measurementUnits nvarchar(50),
   @limitvalues nvarchar(50),
   @normalvalues nvarchar(50),
   @choicesdescription nvarchar(2000),
   @intro nvarchar(4000),
   @desp nvarchar(max),
   @updatetime datetime,
   @gridvalues nvarchar(2000),
   @shortname nvarchar(50)
as
   insert into [labcheck]
   (
      indicatorname,
      isquantified,
      measurementUnits,
      limitvalues,
      normalvalues,
      choicesdescription,
      Introduction,
      description,      
      updatetime,
	  gridvalues,
	  shortname
   )values(
      @name,
      @isquantified,
      @measurementunits,
      @limitvalues,
      @normalvalues,
      @choicesdescription,
      @intro,
      @desp,
      @updatetime,
	  @gridvalues,
	  @shortname
   )
   
GO



/**********************************************************************
UpdateLabcheck
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateLabcheck') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateLabcheck
GO

create procedure UpdateLabcheck
   @id int,
   @name nvarchar(50),
   @isquantified int,
   @measurementUnits nvarchar(50),
   @limitvalues nvarchar(50),
   @normalvalues nvarchar(50),
   @choicesdescription nvarchar(2000),
   @intro nvarchar(4000),
   @desp nvarchar(max),
   @updatetime datetime,
   @gridvalues nvarchar(2000),
   @shortname nvarchar(50)
as
   UPDATE [labcheck]
   SET [indicatorname] = @name
      ,[isquantified] = @isquantified
      ,[measurementunits] = @measurementunits
      ,[limitvalues] = @limitvalues
      ,[normalvalues] = @normalvalues
      ,[choicesdescription] = @choicesdescription
      ,[Introduction] = @intro
      ,[description] = @desp
      ,[updatetime] = @updatetime
	  ,[gridvalues] = @gridvalues
	  ,[shortname] = @shortname
  WHERE [id]=@id
   
GO


/*****************************************************************
GetLabcheckCount
*****************************************************************/


if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetLabcheckCount') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetLabcheckCount
GO

create procedure GetLabcheckCount
   @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   if @condstr <> ''
     begin
       set @strSQL = 'select count([id]) from [labcheck]	where @condstr'   
     end
   else
     begin
       set @strSQL = 'select count([id]) from [labcheck]'   
     end
   exec(@strSQL)
GO


/**********************************************************************
  GetLabcheck
**********************************************************************/	 
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetLabcheck') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetLabcheck
GO

create procedure GetLabcheck
   @id int
as
   select 
        'Id' = [id],
        'Name' = [indicatorname],
        'Intro' = [Introduction],
        'Isquantified' = [isquantified],
        'MeasurementUnits' = [measurementunits],
        'limitvalues' = [limitvalues],
        'normalvalues' = [normalvalues],
        'ChoicesDescription' = [choicesdescription],
        'Desp' = [description],
        'UpdateTime' = [updatetime],
		'GridValues' = [gridvalues],
		'ShortName' = [shortname]
   from 
        [labcheck]
   where [id]=@id
   
GO

/**********************************************************************
  DelLabcheck
**********************************************************************/	 
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelLabcheck') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelLabcheck
GO

create procedure DelLabcheck
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'delete from [labcheck] where ' + @condstr
   exec(@strSQL)
GO


--region [dbo].[GetLabcheckList]

---------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLabcheckList]
---------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLabcheckList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLabcheckList]
GO

CREATE PROCEDURE [dbo].[GetLabcheckList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Labcheck].id) 
FROM
	[Labcheck] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	''Id'' = [Labcheck].[id],
	''Name'' = [IndicatorName],
	''Intro'' = [Labcheck].[Introduction],
	''UpdateTime'' = [Labcheck].[UpdateTime],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM
		[Labcheck]	
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[GetLabcheckListByDisease]

---------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLabcheckListByDisease]
---------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLabcheckListByDisease]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLabcheckListByDisease]
GO

CREATE PROCEDURE [dbo].[GetLabcheckListByDisease]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Labcheck].id) 
FROM
	[Labcheck], [DiseaseLabcheck]
WHERE
	[Labcheck].[Id] = [DiseaseLabcheck].[LabcheckId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	''Id'' = [Labcheck].[id],
	''Name'' = [IndicatorName],
	''Intro'' = [Labcheck].[Introduction],
    ''Isquantified'' = [Labcheck].[isquantified],
    ''MeasurementUnits'' = [Labcheck].[measurementunits],
    ''LimitValues'' = [Labcheck].[LimitValues],
    ''NormalValues'' = [Labcheck].[NormalValues],
	''UpdateTime'' = [Labcheck].[UpdateTime],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM
		[Labcheck], [DiseaseLabcheck]
	WHERE
		[Labcheck].[Id] = [DiseaseLabcheck].[LabcheckId] 	
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion
